#!/usr/bin/python
# -*- coding: utf-8 -*-

# @File        :   db_util.py
# @Version     :   1.0
# @Author      :   zhfnss
# @Email       :   mklv2005@163.com
# @Time        :   2021/03/30 09:53:26
# Description  :   

from sqlalchemy import create_engine, exists, and_
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import SingletonThreadPool

from model.weather_flag import WeatherFlag
from model.weather_info import WeatherInfo


class DbUtil(object):

    def __init__(self):
        super().__init__()
        # 默认使用sqllite，也可以改为mysql或其它数据库
        # engine = create_engine('mysql://scott:tiger@localhost/foo')
        # self.engine = create_engine(
        #     'sqlite:///db\\weather_info.db?check_same_thread=False',
        #     poolclass=SingletonThreadPool,
        #     connect_args={'check_same_thread': False},
        #     echo=True
        # )
        self.engine = create_engine(
            "mysql+pymysql://root:HEbd3cHHuPshKBHT@localhost:3306/watchdog",
            echo=False
        )
        _Session = sessionmaker(bind=self.engine)
        self.session = _Session()

    def weather_exists(self, city_id, weather_date):
        return self.session.query(
            exists().where(
                and_(
                    WeatherInfo.city_id == city_id,
                    WeatherInfo.weather_date == weather_date
                )
            )
        ).scalar()

    def weather_flag_exists(self, weather_flag):
        """查询天气名称是否存在，存在返回id，否则返回None"""
        flag_id = db.session.query(WeatherFlag.id).filter(WeatherFlag.flag_name == weather_flag).one_or_none()
        if flag_id:
            return flag_id[0]
        else:
            return None


db = DbUtil()
